﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PMQLCHH.DAO;
using System.Data.OleDb;
using System.Data;
using System.Collections;
using System.Data.Sql;
using System.Data.SqlClient;

namespace PMQLCHH.DAO
{
    public class NhaCungCapDAO
    {
        // chang biet xu li bắt sự kiện ở đâu nzz  :(
        // cai nay de bi loi lắm ae coi chừng, nếu bị lỗi ở đây pm tôi  
        public static void ExPortNhaCungCap()
        {
            DataTable dtMainSQLData = NhaCungCapDAO.LoadListNhaCungCap();
            DataColumnCollection dcCollection = dtMainSQLData.Columns;
            // Export Data into EXCEL Sheet
            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            ExcelApp.Application.Workbooks.Add(Type.Missing);
            // ExcelApp.Cells.CopyFromRecordset(objRS);

            for (int i = 1; i < dtMainSQLData.Rows.Count + 1; i++)
            {
                for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
                {
                    if (i == 1)
                        ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
                    else
                        ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 1][j - 1].ToString();
                }
            }
            ExcelApp.ActiveWorkbook.SaveCopyAs("D:\\test.xls");
            ExcelApp.ActiveWorkbook.Saved = true;
            ExcelApp.Quit();
        }
        public static bool InsertNhaCungCap(NhaCungCapDTO ncc)
        {
            bool kq;
            string sql = "";
            sql += String.Format("insert into NhaCungCap(MaNhaCungCap,TenNhaCungCap,DiaChi,SoDienThoai,GhiChu,TrangThai,NgayLap) values('{0}',N'{1}',N'{2}','{3}',N'{4}',{5},'{6}')", ncc.MaNCC, ncc.TenNCC, ncc.DIACHI_NCC, ncc.SODIENTHOAI_NCC, ncc.GHICHU_NCC, ncc.TrangThai, ncc.NgayLap);
            kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }

        public static bool UpdateNhaCungCap(NhaCungCapDTO ncc)
        {
            bool kq;
            string sql = "";
            sql += string.Format("update NhaCungCap set DiaChi = '" + ncc.DIACHI_NCC + "', SoDienThoai =  '" + ncc.SODIENTHOAI_NCC + "', TenNhaCungCap = '" + ncc.TenNCC +"', GhiChu = '" + ncc.GHICHU_NCC +"' where MaNhaCungCap = '" + ncc.MaNCC + "'");
            kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }

        public static bool UpdateNhaCungCap(string MaNCC)
        {
            bool kq;
            string sql = "";
            sql += string.Format("update NhaCungCap set TrangThai = 0 where MaNhaCungCap = '" + MaNCC + "'");
            kq = KetNoiCSDLDAO.ExecuteNonQuery(sql);
            return kq;
        }
        public static DataTable LoadListNhaCungCap()
        {
            string sql = "Select MaNhaCungCap,TenNhaCungCap,DiaChi,SoDienThoai,GhiChu, NgayLap from NhaCungCap where trangthai = 1";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable LoadListNhaCungCap_trangthai()
        {
            string sql = "Select MaNhaCungCap,TenNhaCungCap,DiaChi,SoDienThoai,GhiChu, NgayLap from NhaCungCap";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable GetMaNhaCungCap()
        {
            string sql = "Select top 1 MaNhaCungCap from NhaCungCap order by MaNhaCungCap desc";
            DataTable dt = KetNoiCSDLDAO.ExecuteQuery(sql);
            return dt;
        }

        // Chuoi tim gồm có: Ten, diachi, dt
        public static DataTable TimKiemNhaCungCap(string LoaiTim, string TenTim)
        {
            string temp = string.Empty;
            if (LoaiTim == "Tên Nhà Cung Cấp")
                temp = "TenNhaCungCap";
            else if (LoaiTim == "Địa Chỉ")
                temp = "DiaChi";
             else if (LoaiTim == "Ngày Thêm")
                temp = "NgayThem";
             else if (LoaiTim == "Số điện thoại")
                temp = "SoDienThoai";

            DataTable dt = new DataTable();
            if (temp != string.Empty && LoaiTim != string.Empty)
            {
                //Select NhaCungCap.TenNhaCungCap,NhaCungCap.DiaChi,NhaCungCap.SoDienThoai,NhaCungCap.GhiChu, NhaCungCap.NgayLap from NhaCungCap where NhaCungCap.SoDienThoai like '%43%'
                string sql = "select NhaCungCap.MaNhaCungCap,NhaCungCap.TenNhaCungCap, NhaCungCap.DiaChi,NhaCungCap.SoDienThoai,NhaCungCap.GhiChu, NhaCungCap.NgayLap from NhaCungCap where NhaCungCap.TrangThai = 1 and NhaCungCap." + temp + " like '%" + TenTim + "%'";
               // dt = KetNoiCSDLDAO.ExecuteQuery(sql);
                dt = KetNoiCSDLDAO.ExecuteQuery(sql);
                return dt;
            }
            else
                return dt;
        }
    }
}
